CREATE OR REPLACE FUNCTION MODERN.unwrap_profile_row
(
	p_profile_id NUMBER
)
RETURN MODERN.T_IKL_TO_EXCEL
IS
	ID NUMBER := 0;
	cnt NUMBER := 0;
	card_num NVARCHAR2(100) := '';
	crim_num NVARCHAR2(100) := '';
	person NVARCHAR2(100) := '';
	state NVARCHAR2(1000) := '';
	organ NVARCHAR2(300) := '';
	ISSUE NVARCHAR2(100) := '';
	exam_num NVARCHAR2(100) := '';
	dt NVARCHAR2(100) := '';
	ExpertName NVARCHAR2(100) := '';
	Locus1_Name NVARCHAR2(24) := '';
	Locus1_A NVARCHAR2(4);
	Locus1_B NVARCHAR2(4);
	Locus2_Name NVARCHAR2(24);
	Locus2_A NVARCHAR2(4);
	Locus2_B NVARCHAR2(4);
	Locus3_Name NVARCHAR2(24);
	Locus3_A NVARCHAR2(4);
	Locus3_B NVARCHAR2(4);
	Locus4_Name NVARCHAR2(24);
	Locus4_A NVARCHAR2(4);
	Locus4_B NVARCHAR2(4);
	Locus5_Name NVARCHAR2(24);
	Locus5_A NVARCHAR2(4);
	Locus5_B NVARCHAR2(4);
	Locus6_Name NVARCHAR2(24);
	Locus6_A NVARCHAR2(4);
	Locus6_B NVARCHAR2(4);
	Locus7_Name NVARCHAR2(24);
	Locus7_A NVARCHAR2(4);
	Locus7_B NVARCHAR2(4);
	Locus8_Name NVARCHAR2(24);
	Locus8_A NVARCHAR2(4);
	Locus8_B NVARCHAR2(4);
	Locus9_Name NVARCHAR2(24);
	Locus9_A NVARCHAR2(4);
	Locus9_B NVARCHAR2(4);
	Locus10_Name NVARCHAR2(24);
	Locus10_A NVARCHAR2(4);
	Locus10_B NVARCHAR2(4);
	Locus11_Name NVARCHAR2(24);
	Locus11_A NVARCHAR2(4);
	Locus11_B NVARCHAR2(4);
	Locus12_Name NVARCHAR2(24);
	Locus12_A NVARCHAR2(4);
	Locus12_B NVARCHAR2(4);
	Locus13_Name NVARCHAR2(24);
	Locus13_A NVARCHAR2(4);
	Locus13_B NVARCHAR2(4);
	Locus14_Name NVARCHAR2(24);
	Locus14_A NVARCHAR2(4);
	Locus14_B NVARCHAR2(4);
	Locus15_Name NVARCHAR2(24);
	Locus15_A NVARCHAR2(4);
	Locus15_B NVARCHAR2(4);
	Locus16_Name NVARCHAR2(24);
	Locus16_A NVARCHAR2(4);
	Locus16_B NVARCHAR2(4);
	Locus17_Name NVARCHAR2(24);
	Locus17_A NVARCHAR2(4);
	Locus17_B NVARCHAR2(4);
	Locus18_Name NVARCHAR2(24);
	Locus18_A NVARCHAR2(4);
	Locus18_B NVARCHAR2(4);
	Locus19_Name NVARCHAR2(24);
	Locus19_A NVARCHAR2(4);
	Locus19_B NVARCHAR2(4);
	Locus20_Name NVARCHAR2(24);
	Locus20_A NVARCHAR2(4);
	Locus20_B NVARCHAR2(4);
	Locus21_Name NVARCHAR2(24);
	Locus21_A NVARCHAR2(4);
	Locus21_B NVARCHAR2(4);
	Locus22_Name NVARCHAR2(24);
	Locus22_A NVARCHAR2(4);
	Locus22_B NVARCHAR2(4);
	Locus23_Name NVARCHAR2(24);
	Locus23_A NVARCHAR2(4);
	Locus23_B NVARCHAR2(4);
	Locus24_Name NVARCHAR2(24);
	Locus24_A NVARCHAR2(4);
	Locus24_B NVARCHAR2(4);
BEGIN
	FOR rec IN (
		SELECT
			c."ID",
			c.card_num,
			c.crim_num,
			i.person,
			u2.artcl || ' ' || u2.note     state,
			o.note                         organ,
			d."NAME" || ' ' || d.address || ' ' || d.phone ISSUE,
			c.exam_num,
			TRUNC(c.exam_date)             dt,
			e2.surname || ' ' || e2."NAME" || ' ' || e2.patronic ExpertName,
			l."NAME"                       locus,
			CASE  WHEN COUNT(a."ID") > 1 THEN MIN(a.name) ELSE MIN(a.name) END
			allele1,
			CASE  WHEN COUNT(a."ID") > 1 THEN MAX(a.name) ELSE MIN(a.name) END
			allele2
		FROM
			modern.card c
			LEFT JOIN modern.ikl i ON i."ID" = c."ID"
			LEFT JOIN modern.card_ukitem cu ON cu.card_id = c."ID"
			LEFT JOIN modern."ORGANIZATION" o ON o."ID" = c.org_id
			LEFT JOIN modern.ukarticl u2 ON u2."ID" = cu.ukitem_id
			LEFT JOIN modern.expert e2 ON e2."ID" = c.expert_id
			LEFT JOIN modern.division d ON d."ID" = e2.division_id
			LEFT JOIN modern.chk_allele ca ON ca.profile_id = c.id
			LEFT JOIN modern.allele a ON a."ID" = ca.allele_id
			LEFT JOIN modern.locus l ON l."ID" = ca.locus_id
		WHERE
			c."ID" = p_profile_id
		GROUP BY
			c."ID",
			c.card_num,
			c.crim_num,
			i.person,
			u2.artcl || ' ' || u2.note,
			o.note,
			d."NAME" || ' ' || d.address || ' ' || d.phone,
			c.exam_num,
			TRUNC(c.exam_date),
			e2.surname || ' ' || e2."NAME" || ' ' || e2.patronic,
			l."NAME"
		ORDER BY
			l."NAME"
	)
	LOOP
		ID := rec.id;
		CASE  WHEN cnt = 0 THEN card_num := rec.card_num;
		crim_num := rec.crim_num;
		person := rec.person;
		state := rec.state;
		organ := rec.organ;
		ISSUE := rec.ISSUE;
		exam_num := rec.exam_num;
		dt := rec.dt;
		ExpertName := rec.ExpertName;
		Locus1_Name := rec.locus;
		Locus1_A := rec.allele1;
		Locus1_B := rec.allele2;
		WHEN cnt = 1 THEN
		Locus2_Name := rec.locus;
		Locus2_A := rec.allele1;
		Locus2_B := rec.allele2;
		WHEN cnt = 2 THEN
		Locus3_Name := rec.locus;
		Locus3_A := rec.allele1;
		Locus3_B := rec.allele2;
		WHEN cnt = 3 THEN
		Locus4_Name := rec.locus;
		Locus4_A := rec.allele1;
		Locus4_B := rec.allele2;
		WHEN cnt = 4 THEN
		Locus5_Name := rec.locus;
		Locus5_A := rec.allele1;
		Locus5_B := rec.allele2;
		WHEN cnt = 5 THEN
		Locus6_Name := rec.locus;
		Locus6_A := rec.allele1;
		Locus6_B := rec.allele2;
		WHEN cnt = 6 THEN
		Locus7_Name := rec.locus;
		Locus7_A := rec.allele1;
		Locus7_B := rec.allele2;
		WHEN cnt = 7 THEN
		Locus8_Name := rec.locus;
		Locus8_A := rec.allele1;
		Locus8_B := rec.allele2;
		WHEN cnt = 8 THEN
		Locus9_Name := rec.locus;
		Locus9_A := rec.allele1;
		Locus9_B := rec.allele2;
		WHEN cnt = 9 THEN
		Locus10_Name := rec.locus;
		Locus10_A := rec.allele1;
		Locus10_B := rec.allele2;
		WHEN cnt = 10 THEN
		Locus11_Name := rec.locus;
		Locus11_A := rec.allele1;
		Locus11_B := rec.allele2;
		WHEN cnt = 11 THEN
		Locus12_Name := rec.locus;
		Locus12_A := rec.allele1;
		Locus12_B := rec.allele2;
		WHEN cnt = 12 THEN
		Locus13_Name := rec.locus;
		Locus13_A := rec.allele1;
		Locus13_B := rec.allele2;
		WHEN cnt = 13 THEN
		Locus14_Name := rec.locus;
		Locus14_A := rec.allele1;
		Locus14_B := rec.allele2;
		WHEN cnt = 14 THEN
		Locus15_Name := rec.locus;
		Locus15_A := rec.allele1;
		Locus15_B := rec.allele2;
		WHEN cnt = 15 THEN
		Locus16_Name := rec.locus;
		Locus16_A := rec.allele1;
		Locus16_B := rec.allele2;
		WHEN cnt = 16 THEN
		Locus17_Name := rec.locus;
		Locus17_A := rec.allele1;
		Locus17_B := rec.allele2;
		WHEN cnt = 17 THEN
		Locus18_Name := rec.locus;
		Locus18_A := rec.allele1;
		Locus18_B := rec.allele2;
		WHEN cnt = 18 THEN
		Locus19_Name := rec.locus;
		Locus19_A := rec.allele1;
		Locus19_B := rec.allele2;
		WHEN cnt = 19 THEN
		Locus20_Name := rec.locus;
		Locus20_A := rec.allele1;
		Locus20_B := rec.allele2;
		WHEN cnt = 20 THEN
		Locus21_Name := rec.locus;
		Locus21_A := rec.allele1;
		Locus21_B := rec.allele2;
		WHEN cnt = 21 THEN
		Locus22_Name := rec.locus;
		Locus22_A := rec.allele1;
		Locus22_B := rec.allele2;
		WHEN cnt = 22 THEN
		Locus23_Name := rec.locus;
		Locus23_A := rec.allele1;
		Locus23_B := rec.allele2;
		WHEN cnt = 23 THEN
		Locus24_Name := rec.locus;
		Locus24_A := rec.allele1;
		Locus24_B := rec.allele2;
		ELSE
		NULL;
	END CASE ;
	cnt := cnt + 1;
END
LOOP;
	RETURN
	modern.t_ikl_to_excel(
		id,
		card_num,	--"card_num" NVARCHAR2(100),
		crim_num,	--"c.crim_num" NVARCHAR2(100),
		person,	--"surname" NVARCHAR2(100),
		NULL,	--"patronymic" NVARCHAR2(100),
		NULL,	--"name" NVARCHAR2(100),
		state,	--"crim_state" NVARCHAR2(500),
		organ,	--"organization" NVARCHAR2(200),
		ISSUE,	--"expert_otdel" NVARCHAR2(500),
		exam_num,	--"expert_num" NVARCHAR2(32),
		dt,	--"expert_date" TIMESTAMP,
		ExpertName,	--"excpert_name" NVARCHAR2(100),
		Locus1_Name,	--"Locus1_Name" NVARCHAR2(24),
		Locus1_A,	--"Locus1_A" NUMBER,
		Locus1_B,	--"Locus1_B" NUMBER,
		Locus2_Name,	--"Locus2_Name" NVARCHAR2(24),
		Locus2_A,	--"Locus2_A" NUMBER,
		Locus2_B,	--"Locus2_B" NUMBER,
		Locus3_Name,	--"Locus3_Name" NVARCHAR2(24),
		Locus3_A,	--"Locus3_A" NUMBER,
		Locus3_B,	--"Locus3_B" NUMBER,
		Locus4_Name,	--"Locus4_Name" NVARCHAR2(24),
		Locus4_A,	--"Locus4_A" NUMBER,
		Locus4_B,	--"Locus4_B" NUMBER,
		Locus5_Name,	--"Locus5_Name" NVARCHAR2(24),
		Locus5_A,	--"Locus5_A" NUMBER,
		Locus5_B,	--"Locus5_B" NUMBER,
		Locus6_Name,	--"Locus6_Name" NVARCHAR2(24),
		Locus6_A,	--"Locus6_A" NUMBER,
		Locus6_B,	--"Locus6_B" NUMBER,
		Locus7_Name,	--"Locus7_Name" NVARCHAR2(24),
		Locus7_A,	--"Locus7_A" NUMBER,
		Locus7_B,	--"Locus7_B" NUMBER,
		Locus8_Name,	--"Locus8_Name" NVARCHAR2(24),
		Locus8_A,	--"Locus8_A" NUMBER,
		Locus8_B,	--"Locus8_B" NUMBER,
		Locus9_Name,	--"Locus9_Name" NVARCHAR2(24),
		Locus9_A,	--"Locus9_A" NUMBER,
		Locus9_B,	--"Locus9_B" NUMBER,
		Locus10_Name,	--"Locus10_Name" NVARCHAR2(24),
		Locus10_A,	--"Locus10_A" NUMBER,
		Locus10_B,	--"Locus10_B" NUMBER,
		Locus11_Name,	--"Locus11_Name" NVARCHAR2(24),
		Locus11_A,	--"Locus11_A" NUMBER,
		Locus11_B,	--"Locus11_B" NUMBER,
		Locus12_Name,	--"Locus12_Name" NVARCHAR2(24),
		Locus12_A,	--"Locus12_A" NUMBER,
		Locus12_B,	--"Locus12_B" NUMBER,
		Locus13_Name,	--"Locus13_Name" NVARCHAR2(24),
		Locus13_A,	--"Locus13_A" NUMBER,
		Locus13_B,	--"Locus13_B" NUMBER,
		Locus14_Name,	--"Locus14_Name" NVARCHAR2(24),
		Locus14_A,	--"Locus14_A" NUMBER,
		Locus14_B,	--"Locus14_B" NUMBER,
		Locus15_Name,	--"Locus15_Name" NVARCHAR2(24),
		Locus15_A,	--"Locus15_A" NUMBER,
		Locus15_B,	--"Locus15_B" NUMBER,
		Locus16_Name,	--"Locus16_Name" NVARCHAR2(24),
		Locus16_A,	--"Locus16_A" NUMBER,
		Locus16_B,	--"Locus16_B" NUMBER,
		Locus17_Name,	--"Locus17_Name" NVARCHAR2(24),
		Locus17_A,	--"Locus17_A" NUMBER,
		Locus17_B,	--"Locus17_B" NUMBER,
		Locus18_Name,	--"Locus18_Name" NVARCHAR2(24),
		Locus18_A,	--"Locus18_A" NUMBER,
		Locus18_B,	--"Locus18_B" NUMBER,
		Locus19_Name,	--"Locus19_Name" NVARCHAR2(24),
		Locus19_A,	--"Locus19_A" NUMBER,
		Locus19_B,	--"Locus19_B" NUMBER,
		Locus20_Name,	--"Locus20_Name" NVARCHAR2(24),
		Locus20_A,	--"Locus20_A" NUMBER,
		Locus20_B,	--"Locus20_B" NUMBER,
		Locus21_Name,	--"Locus21_Name" NVARCHAR2(24),
		Locus21_A,	--"Locus21_A" NUMBER,
		Locus21_B,	--"Locus21_B" NUMBER,
		Locus22_Name,	--"Locus22_Name" NVARCHAR2(24),
		Locus22_A,	--"Locus22_A" NUMBER,
		Locus22_B,	--"Locus22_B" NUMBER,
		Locus23_Name,	--"Locus23_Name" NVARCHAR2(24),
		Locus23_A,	--"Locus23_A" NUMBER,
		Locus23_B,	--"Locus23_B" NUMBER,
		Locus24_Name,	--"Locus24_Name" NVARCHAR2(24),
		Locus24_A,	--"Locus24_A" NUMBER,
		Locus24_B --"Locus24_B" NUMBER
	);
END ;
/
